package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.ScoreDataExcel;
import com.xmy.cultivate.entity.Transcript;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.TranscriptSafetyDiffSet;
import com.xmy.cultivate.entity.TranscriptSafetyScoreSet;
import com.xmy.cultivate.entity.excelEntity.TranscriptScoreExcel;
import com.xmy.cultivate.entity.views.ResNewStuScore;
import com.xmy.cultivate.entity.views.ScoreData;
import com.xmy.cultivate.entity.views.ScoreDataDetail;
import com.xmy.cultivate.respons.ResTranscriptScore;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 成绩管理 Mapper 接口
 * </p>
 *  no_pass_count 不及格
 *  pass_count 优秀
 *  not_calc_pass_count
 *  carry_count 提分
 *
 *  retrogress_count 退步
 * @author hpiggy
 * @since 2023-04-14
 */
public interface TranscriptMapper extends BaseMapper<Transcript> {

    @Select({
            "<script>",
            "SELECT",
            "*,",
            "getStaffNameForId(teacher_id) as teacher_name,",
            "getSchoolName(school_id) as school_name,",
            "SUM(",
            "CASE",
            "WHEN score_end &lt; ${tsSet11.noPassScore}",
            "AND grade_section = 1 ",
            "and subjects_id &lt;&gt;3",
            "AND ((",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            ") ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )) THEN",
            "1 ",
            "WHEN ((score_end &lt; ${tsSet12.noPassScore} and subjects_id &lt;&gt; 3) or (score_end &lt; ${tsSet32.noPassScore} and subjects_id =3)) ",
            "AND grade_section = 2 ",
            "AND ((",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            ") ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )) THEN",
            "1 ELSE 0 ",
            "END ",
            ") AS no_pass_count,",
            "SUM(",
            "CASE",
            "WHEN score_end &gt;= ${tsSet11.passScore}",
            "AND grade_section = 1 ",
            "and subjects_id &lt;&gt;3",
            "AND (",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )) THEN",
            "1 ",
            "WHEN ((score_end &gt;= ${tsSet12.passScore} and subjects_id &lt;&gt; 3) or (score_end &gt;= ${tsSet32.passScore} and subjects_id =3)) ",
            "AND grade_section = 2 ",
            "AND (",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )",
            "OR (subjects_id = 3 and year_class_id = 8 AND quarter_num = 4 and is_statistics =1)",
            ") THEN",
            "1 ELSE 0 ",
            "END ",
            ") AS pass_count,",
            "SUM(",
            "CASE",
            "<when test='tsSet11.includeThree == 0'>",
                "WHEN score_end &gt;= ${tsSet11.passScore}",
                "AND grade_section = 1 ",
                "and subjects_id &lt;&gt;3",
                "AND (",
                "( year_class_id = 3 AND quarter_num = 4 and exam_num = 1  and subjects_id=1 AND is_statistics = 1 )) THEN",
                "1 ",
            "</when>",
            "WHEN ((",
            "score_end >= ${tsSet32.passScore} ",
            "AND subjects_id = 3 ",
            ")) ",
            "AND grade_section = 2 ",
            "AND ((",
            "year_class_id = 8",
            "AND quarter_num = 4 and exam_num = 1",
            "AND is_statistics = 1 ",
            ")) THEN",
            "1 ELSE 0 ",
            "END ",
            ") AS not_calc_pass_count,",
            "SUM(",
            "CASE",
            "WHEN score_end &gt;= score_start_b ",
            "and subjects_id &lt;&gt;3",
            "AND grade_section = 1 ",
            "AND is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "AND score_end &lt; ${tsSet11.carryScore} THEN 1 ",
            "WHEN score_end &gt;= score_start_b ",
            "AND grade_section = 2 ",
            "AND is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "AND (",
            "(score_end &lt; ${tsSet12.carryScore} and subjects_id  &lt;&gt;3) or ",
            "(score_end &lt; ${tsSet32.carryScore} and subjects_id =3 and year_class_id &lt;&gt; 8) OR ( score_end &lt; ${tsSet32.carryScore} AND subjects_id = 3 and year_class_id = 8 AND quarter_num = 4 AND exam_num &lt;&gt; 1 ) OR ( score_end &lt; ${tsSet32.carryScore} AND subjects_id = 3 and year_class_id = 8 AND quarter_num &lt;&gt; 4 )) THEN",
            "1 ELSE 0 ",
            "END ",
            ") AS carry_count,",

            "SUM(",
            "CASE",
            " WHEN ",
            "grade_section = 1 ",
            "and score_end &lt; score_start_b ",
            "and subjects_id &lt;&gt;3",
            " AND is_statistics = 1 ",
            " AND is_statistics_b = 1 ",
            "<when test='tsSet11.retrogressType != 1'>",
                "AND score_end &lt; ${tsSet11.retrogressScore}",
            "</when>",
            "  THEN 1",
            "WHEN",
            "grade_section = 2 ",
            "and score_end &lt; score_start_b ",
            "  AND is_statistics = 1 ",
            "  AND is_statistics_b = 1 ",
            " AND ((subjects_id  &lt;&gt;3 ",
            "<when test='tsSet12.retrogressType != 1'>",
                "and score_end &lt; ${tsSet12.retrogressScore}",
            "</when>",
            ") or (subjects_id =3 ",
            "<when test='tsSet12.retrogressType != 1'>",
                "and score_end &lt; ${tsSet32.retrogressScore}",
            "</when>",
            "and year_class_id &lt;&gt; 8) OR ( subjects_id = 3",
            "<when test='tsSet12.retrogressType != 1'>",
                "and score_end &lt; ${tsSet32.retrogressScore}",
            "</when>",
            "and year_class_id = 8 AND quarter_num = 4 AND exam_num &lt;&gt; 1 )",
            " or (subjects_id =3 ",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet32.retrogressScore}",
            "</when>",
            "and year_class_id &lt;&gt; 8) OR ( subjects_id = 3",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet32.retrogressScore}",
            "</when>",
            "and year_class_id = 8 AND quarter_num &lt;&gt; 4 )",

            ") THEN ",
            "  1 ELSE 0 ",
            "  END ",
            "  ) AS retrogress_count,",

            "SUM(",
            "CASE",
            "WHEN ( is_statistics = 1 AND is_statistics_b = 1 ) ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )",
            "OR ( year_class_id = 8 AND quarter_num = 4 AND subjects_id=3 AND is_statistics = 1 ) THEN",
            "1 ELSE 0 ",
            "END ",
            ") AS stu_reality_count,",
            "SUM(",
            "CASE",
            "WHEN ",
            "<when test='tsSet11.includeThree == 0'>",
                "( year_class_id = 3 and subjects_id=1 AND quarter_num = 4 and exam_num = 1 AND is_statistics = 1 ) or ",
            "</when>",
            "( year_class_id = 8 AND quarter_num = 4 and exam_num = 1 AND subjects_id=3 AND is_statistics = 1 ) THEN",
            "1 ELSE 0 ",
            "END ",
            ") AS not_calc_count,",
            "SUM( CASE WHEN is_statistics = 1 OR is_statistics = 0 THEN 1 ELSE 0 END ) AS student_all_count",
            "FROM",
            "(",
            "<foreach collection='tsSetList' item='item' open='' separator='UNION all' close=''>",
            "SELECT",
            "t01.*,",
            "t02.exam_num,",
            "IFNULL( t02.score, 0 ) score,",
            "IFNULL( t02.score_start, 0 ) score_start,",
            "IFNULL( t02.score_end, 0 ) score_end,",
            "IFNULL( t02.is_statistics, 0 ) is_statistics,",
            "IFNULL( t03.score, 0 ) score_b,",
            "IFNULL( t03.score_start, 0 ) score_start_b,",
            "IFNULL( t03.score_end, 0 ) score_end_b,",
            "IFNULL( t03.is_statistics, 0 ) is_statistics_b ",
            "FROM",
            "(",
            "SELECT",

            "s01.subjects_id,",
            "s01.student_id,",
            "s01.year_part,",
            "s01.quarter_num,",
            "s01.year_class_id,",
            "s01.grade_id,",
            "getGradeSectionForYearClassId ( s01.year_class_id ) AS grade_section,",

            "<when test='dataType!=null and dataType!=&apos;&apos; and dataType != 4 '>",
                "s01.school_id,",
                "s02.teacher_id ",
            "</when>",

            "<when test='dataType!=null and dataType!=&apos;&apos; and dataType == 4 '>",
                // 成绩数据4，跟缴费率一样，大于等于8次课，调出老师在职算调出老师，调出老师离职算原带班所属校区校长
                "s02.teacher_id tack_teacher_id,",
                "( CASE WHEN s03.in_school_id IS NOT NULL THEN s03.in_school_id ELSE s01.school_id END ) AS school_id,",
                "( CASE WHEN s03.schoolmaster_id IS NOT NULL THEN s03.schoolmaster_id ELSE s02.teacher_id END ) AS teacher_id ",
            "</when>",

            "FROM",
            "student_grade AS s01",
            "LEFT JOIN grade AS s02 ON s01.grade_id = s02.id ",

            "<when test='dataType!=null and dataType!=&apos;&apos; and dataType == 4 '>",
                "LEFT JOIN leave_teacher_student AS s03 ON s01.year_part = s03.year_part ",
                "AND s01.quarter_num = s03.quarter_num ",
                "AND s01.subjects_id = s03.subjects_id ",
                "AND s01.student_id = s03.student_id ",
            "</when>",

            "WHERE",
            "s01.year_part = ${yearPart} ",
            "AND s01.quarter_num = ${quarterNum} ",
            "AND s01.is_last = 1 ",
            "AND s01.deleted = 0 ",
            "AND s01.`status` =1 ",
            "and s02.year_class_id in(${item.yearClassIdStr})",
            "and s02.subjects_id in(${item.subjectsIdStr})",
            "AND s01.`reading_status` in (1,5,6) ",
            "AND s01.lesson_type = 1 ",
            "<when test='courseTypeId!=null and courseTypeId!=&apos;&apos; '>",
            "AND s02.`course_type_id` in(${courseTypeId}) ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "AND s02.year_class_id IN (${yearClassId}) ",
            "</when>",
            "GROUP BY",
            "student_id,",
            "subjects_id ",
            ") AS t01",
            "<if test='include == 0'>",
            "INNER JOIN (select * from record_course where year_part = ${yearPart} AND `quarter`= ${quarterNum} and lesson_type = 1 and course_type in (1,4) and lesson_num = 1 and deleted = 0 GROUP BY student_id,subjects_id) as t04 on t01.student_id = t04.student_id and t01.subjects_id = t04.subjects_id",
            "</if>",
            "LEFT JOIN ( SELECT * FROM transcript WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} AND exam_num = ${examNum} AND deleted = 0 GROUP BY student_id,subjects_id) AS t02 ON t01.student_id = t02.student_id ",
            "AND t01.subjects_id = t02.subjects_id",
            "LEFT JOIN ( SELECT * FROM transcript WHERE year_part = ${item.upYearPart} AND quarter_num = ${item.upQuarterNum} AND exam_num = ${item.upExamNum} AND deleted = 0 GROUP BY student_id,subjects_id) AS t03 ON t01.student_id = t03.student_id ",
            "AND t01.subjects_id = t03.subjects_id ",
            "LEFT JOIN transcript_exclude AS t05 ON t05.student_id = t01.student_id AND t05.subjects_id = t01.subjects_id AND t05.year_part = t01.year_part AND t05.quarter_num = t01.quarter_num AND t05.exam_num = t02.exam_num",
            "WHERE t05.id IS NULL ",
            "</foreach>",
            ") AS t ",
            "${ew.customSqlSegment}",
            "</script>"
    })
    public List<ScoreData> scoreData(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("examNum") Integer examNum, @Param("include") Integer include, @Param("courseTypeId") String courseTypeId, @Param(Constants.WRAPPER) Wrapper wrapper, @Param("tsSet11") TranscriptSafetyScoreSet tsSet11, @Param("tsSet12") TranscriptSafetyScoreSet tsSet12, @Param("tsSet21") TranscriptSafetyScoreSet tsSet21, @Param("tsSet22") TranscriptSafetyScoreSet tsSet22, @Param("tsSet32") TranscriptSafetyScoreSet tsSet32, @Param("tsSetList") List<TranscriptSafetyDiffSet> tsSetList,@Param("dataType") String dataType,@Param("yearClassId") String yearClassId);


    @Select({
            "<script>",
            "SELECT",
            "*,",
            "getStaffNameForId(teacher_id) as teacher_name,",
            "getSchoolName(school_id) as school_name,",
            "SUM(",
            "CASE",
            "WHEN score_end &lt; ${tsSet11.noPassScore}",
            "AND grade_section = 1 ",
            "and subjects_id &lt;&gt;3",
            "AND ((",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            ") ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )) THEN",
            "1 ",
            "WHEN ((score_end &lt; ${tsSet12.noPassScore} and subjects_id &lt;&gt; 3) or (score_end &lt; ${tsSet32.noPassScore} and subjects_id =3)) ",
            "AND grade_section = 2 ",
            "AND ((",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            ") ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )) THEN",
            "1 ELSE 0 ",
            "END ",
            ") AS no_pass_count,",
            "SUM(",
            "CASE",
            "WHEN score_end &gt;= ${tsSet11.passScore}",
            "AND grade_section = 1 ",
            "and subjects_id &lt;&gt;3",
            "AND (",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )) THEN",
            "1 ",
            "WHEN ((score_end &gt;= ${tsSet12.passScore} and subjects_id &lt;&gt; 3) or (score_end &gt;= ${tsSet32.passScore} and subjects_id =3)) ",
            "AND grade_section = 2 ",
            "AND (",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )",
            "OR (subjects_id = 3 and year_class_id = 8 AND quarter_num = 4 and is_statistics =1)",
            ") THEN",
            "1 ELSE 0 ",
            "END ",
            ") AS pass_count,",
            "SUM(",
            "CASE",
            "<when test='tsSet11.includeThree == 0'>",
                "WHEN score_end &gt;= ${tsSet11.passScore}",
                "AND grade_section = 1 ",
                "and subjects_id &lt;&gt;3",
                "AND (",
                "( year_class_id = 3 AND quarter_num = 4 AND exam_num = 1 and subjects_id=1 AND is_statistics = 1 )) THEN",
                "1 ",
            "</when>",
            "WHEN ((",
            "score_end >= ${tsSet32.passScore} ",
            "AND subjects_id = 3 ",
            ")) ",
            "AND grade_section = 2 ",
            "AND ((",
            "year_class_id = 8",
            "AND quarter_num = 4 AND exam_num = 1",
            "AND is_statistics = 1 ",
            ")) THEN",
            "1 ELSE 0 ",
            "END ",
            ") AS not_calc_pass_count,",
            "SUM(",
            "CASE",
            "WHEN score_end &gt;= score_start_b ",
            "and subjects_id &lt;&gt;3",
            "AND grade_section = 1 ",
            "AND is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "AND score_end &lt; ${tsSet11.carryScore} THEN 1 ",
            "WHEN score_end &gt;= score_start_b ",
            "AND grade_section = 2 ",
            "AND is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "AND (",
            "(score_end &lt; ${tsSet12.carryScore} and subjects_id &lt;&gt;3) or ",
            "(score_end &lt; ${tsSet32.carryScore} and subjects_id =3 and year_class_id &lt;&gt; 8) OR ( score_end &lt; ${tsSet32.carryScore} AND subjects_id = 3 and year_class_id = 8 AND quarter_num = 4 AND exam_num &lt;&gt; 1) OR ( score_end &lt; ${tsSet32.carryScore} AND subjects_id = 3 and year_class_id = 8 AND quarter_num &lt;&gt; 4) ) THEN",
            "1 ELSE 0 ",
            "END ",
            ") AS carry_count,",
            "SUM(",
            "CASE",
            " WHEN ",
            "grade_section = 1 ",
            "and score_end &lt; score_start_b ",
            "and subjects_id &lt;&gt;3",
            " AND is_statistics = 1 ",
            " AND is_statistics_b = 1 ",
            "<when test='tsSet11.retrogressType != 1'>",
            "AND score_end &lt; ${tsSet11.retrogressScore}",
            "</when>",
            "  THEN 1",
            "WHEN",
            "grade_section = 2 ",
            "and score_end &lt; score_start_b ",
            "  AND is_statistics = 1 ",
            "  AND is_statistics_b = 1 ",
            " AND ((subjects_id  &lt;&gt;3 ",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet12.retrogressScore}",
            "</when>",
            ") or (subjects_id =3 ",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet32.retrogressScore}",
            "</when>",
            "and year_class_id &lt;&gt; 8) OR ( subjects_id = 3",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet32.retrogressScore}",
            "</when>",
            "  and year_class_id = 8 AND quarter_num = 4 AND exam_num &lt;&gt; 1)",
            "or (subjects_id =3 ",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet32.retrogressScore}",
            "</when>",
            "and year_class_id &lt;&gt; 8) OR ( subjects_id = 3",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet32.retrogressScore}",
            "</when>",
            "  and year_class_id = 8 AND quarter_num &lt;&gt; 4)",


            ") THEN  1 ELSE 0 ",
            "  END ",
            "  ) AS retrogress_count,",
            "SUM(",
            "CASE",
            "WHEN ( is_statistics = 1 AND is_statistics_b = 1 ) ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )",
            "OR ( year_class_id = 8 AND quarter_num = 4 AND subjects_id=3 AND is_statistics = 1 ) THEN",
            "1 ELSE 0 ",
            "END ",
            ") AS stu_reality_count,",
            "SUM(",
            "CASE",
            "WHEN ",
            "<when test='tsSet11.includeThree == 0'>",
            "( year_class_id = 3 and subjects_id=1 AND quarter_num = 4 AND exam_num = 1  AND is_statistics = 1 ) or ",
            "</when>",
            "( year_class_id = 8 AND quarter_num = 4 AND exam_num = 1  AND subjects_id=3 AND is_statistics = 1 ) THEN",
            "1 ELSE 0 ",
            "END ",
            ") AS not_calc_count,",
            "SUM( CASE WHEN is_statistics = 1 OR is_statistics = 0 THEN 1 ELSE 0 END ) AS student_all_count",
            "FROM",
            "(",
            "<foreach collection='tsSetList' item='item' open='' separator='UNION all' close=''>",
            "SELECT",
            "t01.*,",
            "t02.exam_num,",
            "IFNULL( t02.score, 0 ) score,",
            "IFNULL( t02.score_start, 0 ) score_start,",
            "IFNULL( t02.score_end, 0 ) score_end,",
            "IFNULL( t02.is_statistics, 0 ) is_statistics,",
            "IFNULL( t03.score, 0 ) score_b,",
            "IFNULL( t03.score_start, 0 ) score_start_b,",
            "IFNULL( t03.score_end, 0 ) score_end_b,",
            "IFNULL( t03.is_statistics, 0 ) is_statistics_b ",
            "FROM",
            "(",
            "SELECT",
            "s01.school_id,",
            "s01.subjects_id,",
            "s01.student_id,",
            "s01.year_part,",
            "s01.quarter_num,",
            "s01.year_class_id,",
            "s01.grade_id,",
            "getGradeSectionForYearClassId ( s01.year_class_id ) AS grade_section,",
            "s02.teacher_id ",
            "FROM",
            "student_grade AS s01",
            "LEFT JOIN grade AS s02 ON s01.grade_id = s02.id ",
            "WHERE",
            "s01.year_part = ${yearPart} ",
            "AND s01.quarter_num = ${quarterNum} ",
            "AND s01.is_last = 1 ",
            "AND s01.deleted = 0 ",
            "AND s01.`status` =1 ",
            "and s02.year_class_id in(${item.yearClassIdStr})",
            "and s02.subjects_id in(${item.subjectsIdStr})",
            "AND s01.`reading_status` in (1,5,6) ",
            "AND s01.lesson_type = 1 ",
            "<when test='courseTypeId!=null and courseTypeId!=&apos;&apos; '>",
            "AND s02.`course_type_id` in(${courseTypeId}) ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "AND s02.year_class_id IN (${yearClassId}) ",
            "</when>",
            "GROUP BY",
            "student_id,",
            "subjects_id ",
            ") AS t01",
            "<if test='include == 0'>",
            "INNER JOIN (select * from record_course where year_part = ${yearPart} AND `quarter`= ${quarterNum} and lesson_type = 1 and course_type in (1,4) and lesson_num = 1 and deleted = 0 GROUP BY student_id,subjects_id) as t04 on t01.student_id = t04.student_id and t01.subjects_id = t04.subjects_id",
            "</if>",
            "LEFT JOIN ( SELECT * FROM transcript WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} AND exam_num = ${examNum} AND deleted = 0 GROUP BY student_id,subjects_id) AS t02 ON t01.student_id = t02.student_id ",
            "AND t01.subjects_id = t02.subjects_id",
            "LEFT JOIN ( SELECT * FROM transcript WHERE year_part = ${item.upYearPart} AND quarter_num = ${item.upQuarterNum} AND exam_num = ${item.upExamNum} AND deleted = 0 GROUP BY student_id,subjects_id) AS t03 ON t01.student_id = t03.student_id ",
            "AND t01.subjects_id = t03.subjects_id ",
            "</foreach>",
            ") AS t ",
            "${ew.customSqlSegment}",
            "</script>"
    })
    public List<ScoreDataExcel> scoreDataExcel(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("examNum") Integer examNum, @Param("include") Integer include, @Param("courseTypeId") String courseTypeId, @Param(Constants.WRAPPER) Wrapper wrapper, @Param("tsSet11") TranscriptSafetyScoreSet tsSet11, @Param("tsSet12") TranscriptSafetyScoreSet tsSet12, @Param("tsSet21") TranscriptSafetyScoreSet tsSet21, @Param("tsSet22") TranscriptSafetyScoreSet tsSet22, @Param("tsSet32") TranscriptSafetyScoreSet tsSet32, @Param("tsSetList") List<TranscriptSafetyDiffSet> tsSetList,@Param("yearClassId") String yearClassId);


    @Select({"<script>",
            "SELECT",
            "*,",
            "getStaffNameForId ( teacher_id ) AS teacher_name,",
            "getSchoolName ( school_id ) AS school_name,",
            "getDictName(year_class_id, 14) as year_class_name",
            "FROM",
            "(",
            "<foreach collection='tsSetList' item='item' open='' separator='UNION all' close=''>",
            "SELECT",
            "t01.*,",
            "IFNULL( t02.score, 0 ) score,",
            "IFNULL( t02.score_start, 0 ) score_start,",
            "IFNULL( t02.score_end, 0 ) score_end,",
            "IFNULL( t02.is_statistics, 0 ) is_statistics,",
            "IFNULL( t03.score, 0 ) score_b,",
            "IFNULL( t03.score_start, 0 ) score_start_b,",
            "IFNULL( t03.score_end, 0 ) score_end_b,",
            "IFNULL( t03.is_statistics, 0 ) is_statistics_b,",
            "${item.examNum} as exam_num,",
            "${item.upYearPart} as up_year_part,",
            "${item.upQuarterNum} as up_quarter_num,",
            "${item.upExamNum} as up_exam_num",
            "FROM",
            "(",
            "SELECT",
            "s01.subjects_id,",
            "s01.student_id,",
            "s01.year_part,",
            "s01.quarter_num,",
            "s01.year_class_id,",
            "s01.grade_id,",
            "s03.`name` as student_name, ",
            "getGradeSectionForYearClassId ( s01.year_class_id ) AS grade_section,",
            //就读学校
            "sas.`name` AS reade_school,",

            "<when test='dataType!=null and dataType!=&apos;&apos; and dataType != 4 '>",
                "s01.school_id,",
                "s02.teacher_id ",
            "</when>",

            "<when test='dataType!=null and dataType!=&apos;&apos; and dataType == 4 '>",
                // 成绩数据4，跟缴费率一样，大于等于8次课，调出老师在职算调出老师，调出老师离职算原带班所属校区校长
                "s02.teacher_id tack_teacher_id,",
                "( CASE WHEN s04.in_school_id IS NOT NULL THEN s04.in_school_id ELSE s01.school_id END ) AS school_id,",
                "( CASE WHEN s04.schoolmaster_id IS NOT NULL THEN s04.schoolmaster_id ELSE s02.teacher_id END ) AS teacher_id ",
            "</when>",

            "FROM",
            "student_grade AS s01",
            "LEFT JOIN grade AS s02 ON s01.grade_id = s02.id ",
            "LEFT JOIN student AS s03 ON s01.student_id = s03.id",
            //就读学校
            "LEFT JOIN student_at_school AS sas ON sas.id = s03.reading_school_id",
            "<when test='dataType!=null and dataType!=&apos;&apos; and dataType == 4 '>",
                "LEFT JOIN leave_teacher_student AS s04 ON s01.year_part = s04.year_part ",
                "AND s01.quarter_num = s04.quarter_num ",
                "AND s01.subjects_id = s04.subjects_id ",
                "AND s01.student_id = s04.student_id ",
            "</when>",
            "WHERE",
            "s01.year_part = ${yearPart} ",
            "AND s01.quarter_num = ${quarterNum} ",
            "AND s01.is_last = 1 ",
            "AND s01.deleted = 0 ",
            "AND s01.`status` =1 ",
            "and s02.year_class_id in(${item.yearClassIdStr})",
            "and s02.subjects_id in(${item.subjectsIdStr})",
            "AND s01.`reading_status` in (1,5,6) ",
            "AND s01.lesson_type = 1 ",
            "<when test='courseTypeId!=null and courseTypeId!=&apos;&apos; '>",
            "AND s02.`course_type_id` in(${courseTypeId}) ",
            "</when>",
            "<when test='studentName!=null and studentName!=&apos;&apos; '>",
            "AND s03.`name` like '%${studentName}%'",
            "</when>",
            "GROUP BY",
            "student_id,",
            "subjects_id ",
            ") AS t01",
            "<if test='include == 0'>",
            "INNER JOIN (select * from record_course where year_part = ${yearPart} AND `quarter`= ${quarterNum} and lesson_type = 1 and course_type in (1,4) and lesson_num = 1 and deleted = 0 GROUP BY student_id,subjects_id) as t04 on t01.student_id = t04.student_id and t01.subjects_id = t04.subjects_id",
            "</if>",
            "LEFT JOIN ( SELECT * FROM transcript WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} AND exam_num = ${examNum} AND deleted = 0 GROUP BY student_id,subjects_id) AS t02 ON t01.student_id = t02.student_id ",
            "AND t01.subjects_id = t02.subjects_id",
            "LEFT JOIN ( SELECT * FROM transcript WHERE year_part = ${item.upYearPart} AND quarter_num = ${item.upQuarterNum} AND exam_num = ${item.upExamNum} AND deleted = 0 GROUP BY student_id,subjects_id) AS t03 ON t01.student_id = t03.student_id ",
            "AND t01.subjects_id = t03.subjects_id ",
            "LEFT JOIN transcript_exclude AS t05 ON t05.student_id = t01.student_id AND t05.subjects_id = t01.subjects_id AND t05.year_part = t01.year_part AND t05.quarter_num = t01.quarter_num AND t05.exam_num = t02.exam_num",
            "WHERE t05.id IS NULL ",
            "</foreach>",
            ") AS t",
            "${ew.customSqlSegment}",
            "</script>"})
    public IPage<ScoreDataDetail> scoreDataDetail(IPage<?> page, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("examNum") Integer examNum,@Param("include") Integer include, @Param("studentName") String studentName,@Param("courseTypeId") String courseTypeId, @Param(Constants.WRAPPER) Wrapper wrapper, @Param("tsSetList") List<TranscriptSafetyDiffSet> tsSetList,@Param("dataType") String dataType);


    @Select("select a.* from transcript as a LEFT JOIN grade as b on a.grade_id = b.id where a.year_class_id<>b.year_class_id")
    public List<Transcript> getYearClassIdErrorList();

    /**
     * 及格与不及格
     *
     * @param page
     * @param yearPart
     * @param quarterNum
     * @param examNum
     * @param wrapper
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "* ",
            "FROM",
            "(",
            "SELECT",
            "*,",
            "getStaffNameForId ( teacher_id ) AS teacher_name,",
            "getSchoolName ( school_id ) AS school_name,",
            "getDictName ( year_class_id, 14 ) AS year_class_name,",
            "CASE",
            "",
            "WHEN score_end &lt; ${tsSet11.noPassScore} ",
            "AND grade_section = 1 ",
            "and subjects_id &lt;&gt;3",
            "AND ((",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            ") ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )) THEN",
            "1 ",
            "WHEN ((score_end &lt; ${tsSet12.noPassScore} and subjects_id &lt;&gt; 3) or (score_end &lt; ${tsSet32.noPassScore} and subjects_id =3)) ",
            "AND grade_section = 2 ",
            "AND ((",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            ") ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )) THEN",
            "1 ELSE 0 ",
            "END AS no_pass_count,",
            "CASE",
            "",
            "WHEN score_end &gt;= ${tsSet11.noPassScore} ",
            "AND grade_section = 1 ",
            "and subjects_id &lt;&gt;3",
            "AND (",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )) THEN",
            "1 ",
            "WHEN ((score_end &gt;= ${tsSet12.noPassScore} and subjects_id &lt;&gt; 3) or (score_end &gt;= ${tsSet32.noPassScore} and subjects_id =3))",
            "AND grade_section = 2 ",
            "AND (",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )) THEN",
            "1 ELSE 0 ",
            "END AS pass_count ",
            "FROM",
            "(",
            "<foreach collection='tsSetList' item='item' open='' separator='UNION all' close=''>",
            "SELECT",
            "t01.*,",
            "t02.exam_num,",
            "IFNULL( t02.score, 0 ) score,",
            "IFNULL( t02.score_start, 0 ) score_start,",
            "IFNULL( t02.score_end, 0 ) score_end,",
            "IFNULL( t02.is_statistics, 0 ) is_statistics,",
            "IFNULL( t03.score, 0 ) score_b,",
            "IFNULL( t03.score_start, 0 ) score_start_b,",
            "IFNULL( t03.score_end, 0 ) score_end_b,",
            "IFNULL( t03.is_statistics, 0 ) is_statistics_b ",
            "FROM",
            "(",
            "SELECT",
            "s01.subjects_id,",
            "s01.student_id,",
            "s01.year_part,",
            "s01.quarter_num,",
            "s01.year_class_id,",
            "s01.grade_id,",
            "s03.`name` AS student_name, ",
            "getGradeSectionForYearClassId ( s01.year_class_id ) AS grade_section,",

            "<when test='dataType!=null and dataType!=&apos;&apos; and dataType != 4 '>",
                "s01.school_id,",
                "s02.teacher_id ",
            "</when>",

            "<when test='dataType!=null and dataType!=&apos;&apos; and dataType == 4 '>",
                // 成绩数据4，跟缴费率一样，大于等于8次课，调出老师在职算调出老师，调出老师离职算原带班所属校区校长
                "s02.teacher_id tack_teacher_id,",
                "( CASE WHEN s04.in_school_id IS NOT NULL THEN s04.in_school_id ELSE s01.school_id END ) AS school_id,",
                "( CASE WHEN s04.schoolmaster_id IS NOT NULL THEN s04.schoolmaster_id ELSE s02.teacher_id END ) AS teacher_id ",
            "</when>",

            "FROM",
            "student_grade AS s01",
            "LEFT JOIN grade AS s02 ON s01.grade_id = s02.id",
            "LEFT JOIN student AS s03 ON s01.student_id = s03.id ",
            "<when test='dataType!=null and dataType!=&apos;&apos; and dataType == 4 '>",
                "LEFT JOIN leave_teacher_student AS s04 ON s01.year_part = s04.year_part ",
                "AND s01.quarter_num = s04.quarter_num ",
                "AND s01.subjects_id = s04.subjects_id ",
                "AND s01.student_id = s04.student_id ",
            "</when>",
            "WHERE",
            "s01.year_part = ${yearPart} ",
            "AND s01.quarter_num = ${quarterNum} ",
            "AND s01.is_last = 1 ",
            "AND s01.deleted = 0 ",
            "AND s01.`status` = 1 ",
            "and s02.year_class_id in(${item.yearClassIdStr})",
            "and s02.subjects_id in(${item.subjectsIdStr})",
            "AND s01.`reading_status` in (1,5,6) ",
            "AND s01.lesson_type = 1 ",
            "<when test='studentName!=null and studentName!=&apos;&apos; '>",
            "AND s03.`name` like '%${studentName}%'",
            "</when>",
            "<when test='courseTypeId!=null and courseTypeId!=&apos;&apos; '>",
            "AND s02.`course_type_id` in(${courseTypeId}) ",
            "</when>",
            "GROUP BY",
            "student_id,",
            "subjects_id ",
            ") AS t01",
            "<if test='include == 0'>",
            "INNER JOIN (select * from record_course where year_part = ${yearPart} AND `quarter`= ${quarterNum} and lesson_type = 1 and course_type in (1,4) and lesson_num = 1 and deleted = 0 GROUP BY student_id,subjects_id) as t04 on t01.student_id = t04.student_id and t01.subjects_id = t04.subjects_id",
            "</if>",
            "LEFT JOIN ( SELECT * FROM transcript WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} AND exam_num = ${examNum} AND deleted = 0 GROUP BY student_id,subjects_id) AS t02 ON t01.student_id = t02.student_id ",
            "AND t01.subjects_id = t02.subjects_id",
            "LEFT JOIN ( SELECT * FROM transcript WHERE year_part = ${item.upYearPart} AND quarter_num = ${item.upQuarterNum} AND exam_num = ${item.upExamNum} AND deleted = 0 GROUP BY student_id,subjects_id) AS t03 ON t01.student_id = t03.student_id ",
            "AND t01.subjects_id = t03.subjects_id ",
            "LEFT JOIN transcript_exclude AS t05 ON t05.student_id = t01.student_id AND t05.subjects_id = t01.subjects_id AND t05.year_part = t01.year_part AND t05.quarter_num = t01.quarter_num AND t05.exam_num = t02.exam_num",
            "WHERE t05.id IS NULL ",
            "</foreach>",
            ") AS t1 ",
            ") AS t",
            "${ew.customSqlSegment}",
            "</script>"})
    public IPage<ScoreDataDetail> passsSoreDataDetail(IPage<?> page, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("examNum") Integer examNum, @Param("include") Integer include, @Param("studentName") String studentName, @Param("courseTypeId") String courseTypeId, @Param(Constants.WRAPPER) Wrapper wrapper, @Param("tsSet11") TranscriptSafetyScoreSet tsSet11, @Param("tsSet12") TranscriptSafetyScoreSet tsSet12, @Param("tsSet21") TranscriptSafetyScoreSet tsSet21, @Param("tsSet22") TranscriptSafetyScoreSet tsSet22, @Param("tsSet32") TranscriptSafetyScoreSet tsSet32, @Param("tsSetList") List<TranscriptSafetyDiffSet> tsSetList, @Param("dataType") String dataType);


    /**
     * 优秀与不优秀
     *
     * @param page
     * @param yearPart
     * @param quarterNum
     * @param examNum
     * @param wrapper
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "* ",
            "FROM",
            "(",
            "SELECT",
            "*,",
            "getStaffNameForId ( teacher_id ) AS teacher_name,",
            "getSchoolName ( school_id ) AS school_name,",
            "getDictName ( year_class_id, 14 ) AS year_class_name,",

            "CASE",
            "",
            "WHEN score_end &lt; ${tsSet11.passScore} ",
            "AND grade_section = 1 ",
            "and subjects_id &lt;&gt;3",
            "AND ((",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            ") ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )) THEN",
            "1 ",
            "WHEN ((score_end &lt; ${tsSet12.passScore} and subjects_id &lt;&gt; 3) or (score_end &lt; ${tsSet32.passScore} and subjects_id =3))",
            "AND grade_section = 2 ",
            "AND ((",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            ") ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )",
            ") THEN",
            "1 ELSE 0 ",
            "END AS no_pass_count,",

            "CASE",
            "",
            "WHEN score_end &gt;= ${tsSet11.passScore} ",
            "AND grade_section = 1 ",
            "AND (",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )) THEN",
            "1 ",
            "WHEN ((score_end &gt;= ${tsSet12.passScore} and subjects_id &lt;&gt; 3) or (score_end &gt;= ${tsSet32.passScore} and subjects_id =3))",
            "AND grade_section = 2 ",
            "AND (",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )",
            "OR ( subjects_id =3 and year_class_id = 8 AND quarter_num = 4 AND is_statistics = 1 )",
            ") THEN",
            "1 ELSE 0 ",
            "END AS pass_count, ",

            "CASE",
            "",
            "WHEN grade_section = 1 ",
            "and subjects_id &lt;&gt;3",
            "AND is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "<when test='tsSet12.retrogressType != 1'>",
            "AND score_end &lt; ${tsSet11.passScore} ",
            "</when>",
            "THEN",
            "score_end - score_start_b ",
            "WHEN grade_section = 2 ",
            "AND ((subjects_id &lt;&gt;3",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet12.passScore} ",
            "</when>",
            ") or (subjects_id =3 ",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet32.passScore}",
            "</when>",
            " and year_class_id &lt;&gt; 8) OR (subjects_id = 3 ",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet32.passScore}",
            "</when>",
            " and year_class_id = 8 AND quarter_num = 4 AND exam_num &lt;&gt; 1 )",
            " or (subjects_id =3 ",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet32.passScore}",
            "</when>",
            " and year_class_id &lt;&gt; 8) OR (subjects_id = 3 ",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet32.passScore}",
            "</when>",
            " and year_class_id = 8 AND quarter_num &lt;&gt; 4 )",
            ") THEN",
            "score_end - score_start_b ELSE 0",
            "END AS diff_score, ",

            "CASE",
            "",
            "WHEN score_end &gt;= score_start_b ",
            "AND grade_section = 1 ",
            "and subjects_id &lt;&gt;3",
            "AND is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "AND score_end &gt;= ${tsSet11.carryScore} THEN 1",
            "WHEN score_end &gt;= score_start_b ",
            "AND grade_section = 2 ",
            "AND ((score_end &gt;= ${tsSet12.carryScore} and subjects_id &lt;&gt;3) or (score_end &gt;= ${tsSet32.carryScore} and subjects_id =3 and year_class_id &lt;&gt; 8) OR ( score_end &gt;= ${tsSet32.carryScore} AND subjects_id = 3 and year_class_id = 8 AND quarter_num = 4 AND exam_num &lt;&gt; 1) OR ( score_end &gt;= ${tsSet32.carryScore} AND subjects_id = 3 and year_class_id = 8 AND quarter_num &lt;&gt; 4) ) THEN",
            "1 ELSE 0 ",
            "END AS diff_pass_count ",

            "FROM",
            "(",
            "<foreach collection='tsSetList' item='item' open='' separator='UNION all' close=''>",
            "SELECT",
            "t01.*,",
            "t02.exam_num,",
            "IFNULL( t02.score, 0 ) score,",
            "IFNULL( t02.score_start, 0 ) score_start,",
            "IFNULL( t02.score_end, 0 ) score_end,",
            "IFNULL( t02.is_statistics, 0 ) is_statistics,",
            "IFNULL( t03.score, 0 ) score_b,",
            "IFNULL( t03.score_start, 0 ) score_start_b,",
            "IFNULL( t03.score_end, 0 ) score_end_b,",
            "IFNULL( t03.is_statistics, 0 ) is_statistics_b ",
            "FROM",
            "(",
            "SELECT",
            "s01.subjects_id,",
            "s01.student_id,",
            "s01.year_part,",
            "s01.quarter_num,",
            "s01.year_class_id,",
            "s01.grade_id,",
            "getGradeSectionForYearClassId ( s01.year_class_id ) AS grade_section,",
            "s03.`name` AS student_name, ",

            "<when test='dataType!=null and dataType!=&apos;&apos; and dataType != 4 '>",
                "s01.school_id,",
                "s02.teacher_id ",
            "</when>",

            "<when test='dataType!=null and dataType!=&apos;&apos; and dataType == 4 '>",
                // 成绩数据4，跟缴费率一样，大于等于8次课，调出老师在职算调出老师，调出老师离职算原带班所属校区校长
                "s02.teacher_id tack_teacher_id,",
                "( CASE WHEN s04.in_school_id IS NOT NULL THEN s04.in_school_id ELSE s01.school_id END ) AS school_id,",
                "( CASE WHEN s04.schoolmaster_id IS NOT NULL THEN s04.schoolmaster_id ELSE s02.teacher_id END ) AS teacher_id ",
            "</when>",

            "FROM",
            "student_grade AS s01",
            "LEFT JOIN grade AS s02 ON s01.grade_id = s02.id",
            "LEFT JOIN student AS s03 ON s01.student_id = s03.id ",
            "<when test='dataType!=null and dataType!=&apos;&apos; and dataType == 4 '>",
                "LEFT JOIN leave_teacher_student AS s04 ON s01.year_part = s04.year_part ",
                "AND s01.quarter_num = s04.quarter_num ",
                "AND s01.subjects_id = s04.subjects_id ",
                "AND s01.student_id = s04.student_id ",
            "</when>",
            "WHERE",
            "s01.year_part = ${yearPart} ",
            "AND s01.quarter_num = ${quarterNum} ",
            "AND s01.is_last = 1 ",
            "AND s01.deleted = 0 ",
            "AND s01.`status` =1 ",
            "and s02.year_class_id in(${item.yearClassIdStr})",
            "and s02.subjects_id in(${item.subjectsIdStr})",
            "AND s01.`reading_status` in (1,5,6) ",
            "AND s01.lesson_type = 1 ",
            "<when test='studentName!=null and studentName!=&apos;&apos; '>",
            "AND s03.`name` like '%${studentName}%'",
            "</when>",
            "<when test='courseTypeId!=null and courseTypeId!=&apos;&apos; '>",
            "AND s02.`course_type_id` in(${courseTypeId}) ",
            "</when>",
            "GROUP BY",
            "student_id,",
            "subjects_id ",
            ") AS t01",
            "<if test='include == 0'>",
            "INNER JOIN (select * from record_course where year_part = ${yearPart} AND `quarter`= ${quarterNum} and lesson_type = 1 and course_type in (1,4) and lesson_num = 1 and deleted = 0 GROUP BY student_id,subjects_id) as t04 on t01.student_id = t04.student_id and t01.subjects_id = t04.subjects_id",
            "</if>",
            "LEFT JOIN ( SELECT * FROM transcript WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} AND exam_num = ${examNum} AND deleted = 0 GROUP BY student_id,subjects_id) AS t02 ON t01.student_id = t02.student_id ",
            "AND t01.subjects_id = t02.subjects_id",
            "LEFT JOIN ( SELECT * FROM transcript WHERE year_part = ${item.upYearPart} AND quarter_num = ${item.upQuarterNum} AND exam_num = ${item.upExamNum} AND deleted = 0 GROUP BY student_id,subjects_id) AS t03 ON t01.student_id = t03.student_id ",
            "AND t01.subjects_id = t03.subjects_id ",
            "LEFT JOIN transcript_exclude AS t05 ON t05.student_id = t01.student_id AND t05.subjects_id = t01.subjects_id AND t05.year_part = t01.year_part AND t05.quarter_num = t01.quarter_num AND t05.exam_num = t02.exam_num",
            "WHERE t05.id IS NULL ",
            "</foreach>",
            ") AS t1 ",
            ") AS t",
            "${ew.customSqlSegment}",
            "</script>"})
    public IPage<ScoreDataDetail> excellentSoreDataDetail(IPage<?> page, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("examNum") Integer examNum, @Param("include") Integer include, @Param("studentName") String studentName, @Param("courseTypeId") String courseTypeId, @Param(Constants.WRAPPER) Wrapper wrapper, @Param("tsSet11") TranscriptSafetyScoreSet tsSet11, @Param("tsSet12") TranscriptSafetyScoreSet tsSet12, @Param("tsSet21") TranscriptSafetyScoreSet tsSet21, @Param("tsSet22") TranscriptSafetyScoreSet tsSet22, @Param("tsSet32") TranscriptSafetyScoreSet tsSet32, @Param("tsSetList") List<TranscriptSafetyDiffSet> tsSetList, @Param("dataType") String dataType);

    /**
     * 提分
     *
     * @param page
     * @param yearPart
     * @param quarterNum
     * @param examNum
     * @param wrapper
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "*,",
            "getStaffNameForId ( teacher_id ) AS teacher_name,",
            "getSchoolName ( school_id ) AS school_name,",
            "getDictName ( year_class_id, 14 ) AS year_class_name ",
            "FROM",
            "(",
            "SELECT",
            "*,",
            "CASE",
            "",
            "WHEN score_end &gt;= score_start_b ",
            "AND grade_section = 1 ",
            "and subjects_id &lt;&gt;3",
            "AND is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "AND score_end &lt; ${tsSet11.carryScore} THEN 1",
            "WHEN score_end &gt;= score_start_b ",
            "AND grade_section = 2 ",
            "AND ((score_end &lt; ${tsSet12.carryScore} and subjects_id &lt;&gt;3) or (score_end &lt;${tsSet32.carryScore} and subjects_id =3 and year_class_id &lt;&gt; 8) OR ( score_end &lt; ${tsSet32.carryScore} AND subjects_id = 3 and year_class_id = 8 AND quarter_num = 4 AND exam_num &lt;&gt; 1) OR ( score_end &lt; ${tsSet32.carryScore} AND subjects_id = 3 and year_class_id = 8 AND quarter_num &lt;&gt; 4) ) THEN",
            "1 ELSE 0 ",
            "END AS pass_count,",
            "CASE",
            "",
            "WHEN grade_section = 1 ",
            "and subjects_id &lt;&gt;3",
            "AND is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "<when test='tsSet12.retrogressType != 1'>",
            "AND score_end &lt; ${tsSet11.passScore} ",
            "</when>",
            "THEN",
            "score_end - score_start_b ",
            "WHEN grade_section = 2 ",
            "AND ((subjects_id &lt;&gt;3",
            "<when test='tsSet12.retrogressType != 1'>",
                "and score_end &lt; ${tsSet12.passScore} ",
            "</when>",
            ") or (subjects_id =3 ",
            "<when test='tsSet12.retrogressType != 1'>",
                "and score_end &lt; ${tsSet32.passScore}",
            "</when>",
            " and year_class_id &lt;&gt; 8) OR (subjects_id = 3 ",
            "<when test='tsSet12.retrogressType != 1'>",
                "and score_end &lt; ${tsSet32.passScore}",
            "</when>",
            " and year_class_id = 8 AND quarter_num = 4 AND exam_num &lt;&gt; 1 )",
            " or (subjects_id =3 ",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet32.passScore}",
            "</when>",
            " and year_class_id &lt;&gt; 8) OR (subjects_id = 3 ",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet32.passScore}",
            "</when>",
            " and year_class_id = 8 AND quarter_num &lt;&gt; 4 )",

            ") THEN",
            "score_end - score_start_b ELSE 0",
            "END AS diff_score ",
            "FROM",
            "(",
            "<foreach collection='tsSetList' item='item' open='' separator='UNION all' close=''>",
            "SELECT",
            "t01.*,",
            "t02.exam_num,",
            "IFNULL( t02.score, 0 ) score,",
            "IFNULL( t02.score_start, 0 ) score_start,",
            "IFNULL( t02.score_end, 0 ) score_end,",
            "IFNULL( t02.is_statistics, 0 ) is_statistics,",
            "IFNULL( t03.score, 0 ) score_b,",
            "IFNULL( t03.score_start, 0 ) score_start_b,",
            "IFNULL( t03.score_end, 0 ) score_end_b,",
            "IFNULL( t03.is_statistics, 0 ) is_statistics_b ",
            "FROM",
            "(",
            "SELECT",
            "s01.subjects_id,",
            "s01.student_id,",
            "s01.year_part,",
            "s01.quarter_num,",
            "s01.year_class_id,",
            "s01.grade_id,",
            "getGradeSectionForYearClassId ( s01.year_class_id ) AS grade_section,",
            "s03.`name` AS student_name, ",
            "s03.linkman_phone, ",

            "<when test='dataType!=null and dataType!=&apos;&apos; and dataType != 4 '>",
                "s01.school_id,",
                "s02.teacher_id ",
            "</when>",

            "<when test='dataType!=null and dataType!=&apos;&apos; and dataType == 4 '>",
            // 成绩数据4，跟缴费率一样，大于等于8次课，调出老师在职算调出老师，调出老师离职算原带班所属校区校长
                "s02.teacher_id tack_teacher_id,",
                "( CASE WHEN s04.in_school_id IS NOT NULL THEN s04.in_school_id ELSE s01.school_id END ) AS school_id,",
                "( CASE WHEN s04.schoolmaster_id IS NOT NULL THEN s04.schoolmaster_id ELSE s02.teacher_id END ) AS teacher_id ",
            "</when>",

            "FROM",
            "student_grade AS s01",
            "LEFT JOIN grade AS s02 ON s01.grade_id = s02.id",
            "LEFT JOIN student AS s03 ON s01.student_id = s03.id ",
            "<when test='dataType!=null and dataType!=&apos;&apos; and dataType == 4 '>",
                "LEFT JOIN leave_teacher_student AS s04 ON s01.year_part = s04.year_part ",
                "AND s01.quarter_num = s04.quarter_num ",
                "AND s01.subjects_id = s04.subjects_id ",
                "AND s01.student_id = s04.student_id ",
            "</when>",
            "WHERE",
            "s01.year_part = ${yearPart} ",
            "AND s01.quarter_num = ${quarterNum} ",
            "AND s01.is_last = 1 ",
            "AND s01.deleted = 0 ",
            "AND s01.`status` =1 ",
            "and s02.year_class_id in(${item.yearClassIdStr})",
            "and s02.subjects_id in(${item.subjectsIdStr})",
            "AND s01.`reading_status` in (1,5,6) ",
            "AND s01.lesson_type = 1 ",
            "<when test='studentName!=null and studentName!=&apos;&apos; '>",
            "AND s03.`name` like '%${studentName}%'",
            "</when>",
            "<when test='courseTypeId!=null and courseTypeId!=&apos;&apos; '>",
            "AND s02.`course_type_id` in(${courseTypeId}) ",
            "</when>",
            "GROUP BY",
            "student_id,",
            "subjects_id ",
            ") AS t01",
            "<if test='include == 0'>",
            "INNER JOIN (select * from record_course where year_part = ${yearPart} AND `quarter`= ${quarterNum} and lesson_type = 1 and course_type in (1,4) and lesson_num = 1 and deleted = 0 GROUP BY student_id,subjects_id) as t04 on t01.student_id = t04.student_id and t01.subjects_id = t04.subjects_id",
            "</if>",
            "LEFT JOIN ( SELECT * FROM transcript WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} AND exam_num = ${examNum} AND deleted = 0 GROUP BY student_id,subjects_id) AS t02 ON t01.student_id = t02.student_id ",
            "AND t01.subjects_id = t02.subjects_id",
            "LEFT JOIN ( SELECT * FROM transcript WHERE year_part = ${item.upYearPart} AND quarter_num = ${item.upQuarterNum} AND exam_num = ${item.upExamNum} AND deleted = 0 GROUP BY student_id,subjects_id) AS t03 ON t01.student_id = t03.student_id ",
            "AND t01.subjects_id = t03.subjects_id ",
            "LEFT JOIN transcript_exclude AS t05 ON t05.student_id = t01.student_id AND t05.subjects_id = t01.subjects_id AND t05.year_part = t01.year_part AND t05.quarter_num = t01.quarter_num AND t05.exam_num = t02.exam_num",
            "WHERE t05.id IS NULL ",
            "</foreach>",
            ") AS t1 ",
            ") AS t",
            "${ew.customSqlSegment}",
            "</script>"})
    public IPage<ScoreDataDetail> carrySoreDataDetail(IPage<?> page, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("examNum") Integer examNum, @Param("include") Integer include, @Param("studentName") String studentName, @Param("courseTypeId") String courseTypeId, @Param(Constants.WRAPPER) Wrapper wrapper, @Param("tsSet11") TranscriptSafetyScoreSet tsSet11, @Param("tsSet12") TranscriptSafetyScoreSet tsSet12, @Param("tsSet21") TranscriptSafetyScoreSet tsSet21, @Param("tsSet22") TranscriptSafetyScoreSet tsSet22, @Param("tsSet32") TranscriptSafetyScoreSet tsSet32, @Param("tsSetList") List<TranscriptSafetyDiffSet> tsSetList, @Param("dataType") String dataType);



    @Select("select * from transcript where year_part = ${yearPart}  and quarter_num =${quarterNum} and student_id = ${studentId} and exam_num=${examNum}  and deleted = 0 order by subjects_id asc")
    public List<ResTranscriptScore> getStudentScore(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("studentId") Long studentId, @Param("examNum") Integer examNum);

    /**
     * @param schoolId             当前在读课时大于零校区
     * @param yearPart             当前在读课时大于零年份
     * @param quarterNum           当前在读课时大于零季度
     * @param yearPartTranscript   当前录入分数的年份
     * @param quarterNumTranscript 当前录入分数的学期
     * @param examNum              考试名称id:1期中，2期中摸底，3期末，4期末摸底，5市模拟
     * @return
     */
    @Select("select school_name,subjects_name,student_name,year_class_name,score from transcript where year_part = ${yearPartTranscript} and quarter_num = ${quarterNumTranscript} and exam_num = ${examNum} and is_statistics = 1 and deleted = 0 and school_id = ${schoolId} and subjects_id = 1 and student_id in(select student_id from order_detail where year_part = ${yearPart} and quarter_num = ${quarterNum} and `status`<>2 and deleted = 0 and residue_course_count>0 and school_id = ${schoolId})")
    public List<TranscriptScoreExcel> getTranscriptStudentList(@Param("schoolId") Long schoolId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("yearPartTranscript") Integer yearPartTranscript, @Param("quarterNumTranscript") Integer quarterNumTranscript, @Param("examNum") Integer examNum);


    /**
     * 获取在读的学员信息（家长填写成绩推送使用）
     *
     * @param yearPart
     * @param quarterNum
     * @param examNum
     * @return
     */
    @Select({"<script>",
            "SELECT",
                      "* ",
                    "FROM",
                      "(",
                      "SELECT",
                        "t01.*,",
                        "IFNULL( t02.score, 0 ) score,",
                        "IFNULL( t02.score_start, 0 ) score_start,",
                        "IFNULL( t02.score_end, 0 ) score_end,",
                        "IFNULL( t02.is_statistics, 0 ) is_statistics ",
                      "FROM",
                        "(",
                        "SELECT",
                          "s01.school_id,",
                          "s01.subjects_id,",
                          "s01.student_id,",
                          "s01.year_part,",
                          "s01.quarter_num,",
                          "s01.year_class_id,",
                          "getGradeSectionForYearClassId ( s01.year_class_id ) AS grade_section,",
                          "s02.teacher_id ",
                        "FROM",
                          "student_grade AS s01",
                          "LEFT JOIN grade AS s02 ON s01.grade_id = s02.id ",
                        "WHERE",
                          "s01.year_part = ${yearPart} ",
                          "AND s01.quarter_num = ${quarterNum} ",
                          "AND s01.is_last = 1 ",
                          "AND s01.deleted = 0 ",
                          "AND s01.`status` = 1 ",
                          "AND s01.`reading_status` in(1,5,6) ",
                          "AND s01.lesson_type = 1 ",
                        "GROUP BY",
                          "student_id,",
                          "subjects_id ",
                        ") AS t01",
                        "LEFT JOIN ( SELECT * FROM transcript WHERE year_part = ${yearPartWrite} AND quarter_num = ${quarterNumWrite} AND exam_num = ${examNum} AND deleted = 0 ) AS t02 ON t01.student_id = t02.student_id ",
                      "AND t01.subjects_id = t02.subjects_id ",
                      ") AS t",
                        "${ew.customSqlSegment}",
            "</script>"})
    public List<ScoreData> getReadStudent(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("yearPartWrite") Integer yearPartWrite, @Param("quarterNumWrite") Integer quarterNumWrite, @Param("examNum") Integer examNum, @Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 安全值
     *
     * @param yearPart
     * @param quarterNum
     * @param upYearPart
     * @param upQuarterNum
     * @param wrapper
     * @return
     */
    @Select({
            "<script>",
            "SELECT",
            "*,",
            "getStaffNameForId(teacher_id) as teacher_name,",
            "getSchoolName(school_id) as school_name,",
            "SUM(",
            "CASE",
            "",
            "WHEN score_end &lt; ${tsSet11.noPassScore}",
            "AND grade_section = 1 ",
            "and subjects_id &lt;&gt;3",
            "AND ((",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            ") ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )) THEN",
            "1 ",
            "WHEN ((score_end &lt; ${tsSet12.noPassScore} and subjects_id &lt;&gt; 3) or (score_end &lt; ${tsSet32.noPassScore} and subjects_id = 3)) ",
            "AND grade_section = 2 ",
            "AND ((",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            ") ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )) THEN",
            "1 ELSE 0 ",
            "END ",
            ") AS no_pass_count,",
            "SUM(",
            "CASE",
            "",
            "WHEN score_end &gt;= ${tsSet11.passScore}",
            "AND grade_section = 1 ",
            "and subjects_id &lt;&gt;3",
            "AND (",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )) THEN",
            "1 ",
            "WHEN ((score_end &gt;= ${tsSet12.passScore} and subjects_id &lt;&gt; 3) or (score_end &gt;= ${tsSet32.passScore} and subjects_id =3)) ",
            "AND grade_section = 2 ",
            "AND (",
            "is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 )) THEN",
            "1 ELSE 0 ",
            "END ",
            ") AS pass_count,",
            "SUM(",
            "CASE",
            "WHEN score_end &gt;= score_start_b ",
            "and subjects_id &lt;&gt;3",
            "AND grade_section = 1 ",
            "AND is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "AND score_end &lt; ${tsSet11.carryScore} THEN 1 ",
            "WHEN score_end &gt;= score_start_b ",
            "AND grade_section = 2 ",
            "AND is_statistics = 1 ",
            "AND is_statistics_b = 1 ",
            "AND ((score_end &lt; ${tsSet12.carryScore} and subjects_id &lt;&gt;3) or (score_end &lt; ${tsSet32.carryScore} and subjects_id =3 and year_class_id &lt;&gt; 8) OR ( score_end &lt; ${tsSet32.carryScore} AND subjects_id = 3 and year_class_id = 8 AND quarter_num = 4 AND exam_num &lt;&gt; 1)  OR ( score_end &lt; ${tsSet32.carryScore} AND subjects_id = 3 and year_class_id = 8 AND quarter_num &lt;&gt; 4) ) THEN",
            "1 ELSE 0 ",
            "END ",
            ") AS carry_count,",
            "SUM(",
            "CASE",
            " WHEN ",
            "grade_section = 1 ",
            "and score_end &lt; score_start_b ",
            "and subjects_id &lt;&gt;3",
            " AND is_statistics = 1 ",
            " AND is_statistics_b = 1 ",
            "<when test='tsSet11.retrogressType != 1'>",
            "AND score_end &lt; ${tsSet11.retrogressScore}",
            "</when>",
            "  THEN 1",
            "WHEN",
            "grade_section = 2 ",
            "and score_end &lt; score_start_b ",
            "  AND is_statistics = 1 ",
            "  AND is_statistics_b = 1 ",
            " AND ((subjects_id  &lt;&gt;3 ",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet12.retrogressScore}",
            "</when>",
            ") or (subjects_id =3 ",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet32.retrogressScore}",
            "</when>",
            "and year_class_id &lt;&gt; 8) OR ( subjects_id = 3",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet32.retrogressScore}",
            "</when>",
            "  and year_class_id = 8 AND quarter_num = 4 AND exam_num &lt;&gt; 1)",
            "or (subjects_id =3 ",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet32.retrogressScore}",
            "</when>",
            "and year_class_id &lt;&gt; 8) OR ( subjects_id = 3",
            "<when test='tsSet12.retrogressType != 1'>",
            "and score_end &lt; ${tsSet32.retrogressScore}",
            "</when>",
            "  and year_class_id = 8 AND quarter_num &lt;&gt; 4 )",
            " ) THEN 1 ELSE 0 ",
            "  END ",
            "  ) AS retrogress_count,",
            "SUM(",
            "CASE",
            "",
            "WHEN ( is_statistics = 1 AND is_statistics_b = 1 ) ",
            "OR ( year_class_id = 3 AND quarter_num = 4 AND is_statistics = 1 ) THEN",
            "1 ELSE 0 ",
            "END ",
            ") AS stu_reality_count,",
            "SUM(",
            "CASE",
            "WHEN ( year_class_id = 3 and subjects_id=1 AND quarter_num = 4 AND exam_num = 1  AND is_statistics = 1 ) or ( year_class_id = 8 AND quarter_num = 4 AND exam_num = 1  AND subjects_id=3 AND is_statistics = 1 ) THEN",
            "1 ELSE 0 ",
            "END ",
            ") AS not_calc_count,",
            "SUM( CASE WHEN is_statistics = 1 OR is_statistics = 0 THEN 1 ELSE 0 END ) AS student_all_count",
            "FROM",
            "(",
            "SELECT",
            "t01.*,",
            "t02.exam_num,",
            "IFNULL( t02.score, 0 ) score,",
            "IFNULL( t02.score_start, 0 ) score_start,",
            "IFNULL( t02.score_end, 0 ) score_end,",
            "IFNULL( t02.is_statistics, 0 ) is_statistics,",
            "IFNULL( t03.score, 0 ) score_b,",
            "IFNULL( t03.score_start, 0 ) score_start_b,",
            "IFNULL( t03.score_end, 0 ) score_end_b,",
            "IFNULL( t03.is_statistics, 0 ) is_statistics_b ",
            "FROM",
            "(",
            "SELECT",
            "s01.school_id,",
            "s01.subjects_id,",
            "s01.student_id,",
            "s01.year_part,",
            "s01.quarter_num,",
            "s01.year_class_id,",
            "s01.grade_id,",
            "getGradeSectionForYearClassId ( s01.year_class_id ) AS grade_section,",
            "s02.teacher_id ",
            "FROM",
            "student_grade AS s01",
            "LEFT JOIN grade AS s02 ON s01.grade_id = s02.id ",
            "WHERE",
            "s01.year_part = ${yearPart} ",
            "AND s01.quarter_num = ${quarterNum} ",
            "AND s01.is_last = 1 ",
            "AND s01.deleted = 0 ",
            "AND s01.`status` =1 ",
            "AND s01.`reading_status` in (1,5,6) ",
            "AND s01.lesson_type = 1 ",
            "GROUP BY",
            "student_id,",
            "subjects_id ",
            ") AS t01",
            "INNER JOIN (select * from record_course where year_part = ${yearPart} AND `quarter`= ${quarterNum} and lesson_type = 1 and course_type in (1,4) and lesson_num = 1 and deleted = 0 GROUP BY student_id,subjects_id) as t04 on t01.student_id = t04.student_id and t01.subjects_id = t04.subjects_id",
            "LEFT JOIN ( SELECT * FROM transcript WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} AND exam_num = ${examNum} AND deleted = 0 GROUP BY student_id,subjects_id) AS t02 ON t01.student_id = t02.student_id ",
            "AND t01.subjects_id = t02.subjects_id",
            "LEFT JOIN ( SELECT * FROM transcript WHERE year_part = ${upYearPart} AND quarter_num = ${upQuarterNum} AND exam_num = ${upExamNum} AND deleted = 0 GROUP BY student_id,subjects_id) AS t03 ON t01.student_id = t03.student_id ",
            "AND t01.subjects_id = t03.subjects_id ",
            ") AS t ",
            "${ew.customSqlSegment}",
            "</script>"
    })
    public List<ScoreData> securityData(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("examNum") Integer examNum, @Param("upYearPart") Integer upYearPart, @Param("upQuarterNum") Integer upQuarterNum, @Param("upExamNum") Integer upExamNum, @Param(Constants.WRAPPER) Wrapper wrapper, @Param("tsSet11") TranscriptSafetyScoreSet tsSet11,@Param("tsSet12") TranscriptSafetyScoreSet tsSet12,@Param("tsSet21") TranscriptSafetyScoreSet tsSet21,@Param("tsSet22") TranscriptSafetyScoreSet tsSet22,@Param("tsSet32") TranscriptSafetyScoreSet tsSet32);

    /**
     * 批量移入移出
     *
     * @param isEliminate
     * @param studentId
     * @return
     */
    @Select("UPDATE transcript SET is_eliminate = #{isEliminate} WHERE student_id = #{studentId} AND quarter_num = #{quarterNum} AND year_part = #{yearPart} AND school_id = #{schoolId} AND deleted = 0")
    public abstract Integer updateIsEliminate(@Param("isEliminate") String isEliminate, @Param("studentId") String studentId, @Param("quarterNum") String quarterNum, @Param("yearPart") String yearPart, @Param("schoolId") String schoolId);

    /**
     * 根据年级批量移入移出
     *
     * @param isEliminate
     * @param yearClassId
     * @return
     */
    @Select("UPDATE transcript SET is_eliminate = #{isEliminate} WHERE year_class_id = #{yearClassId} AND quarter_num = #{quarterNum} AND year_part = #{yearPart} AND deleted = 0")
    public abstract Integer updateIsEliminateByYearClassId(@Param("isEliminate") String isEliminate, @Param("yearClassId") String yearClassId, @Param("quarterNum") String quarterNum, @Param("yearPart") String yearPart);


    @Select({"<script>",
            "SELECT",
            "tb02.id,",
            "tb01.student_id,",
            "getStudentNameForId ( tb01.student_id ) AS student_name,",
            "getSubjectsName ( tb01.subjects_id ) AS subjects_name,",
            "year_class_name,",
            "score,",
            "score_start,",
            "score_end,",
            "year_class_id_order AS year_class_id,",
            "year_part,",
            "tb02.quarter_num,",
            "tb03.head_img",
            "FROM",
            "(",
            "SELECT",
              "t1.*,",
              "t2.school_id AS school_id_order,",
              "t3.year_class_id AS year_class_id_order ",
            "FROM",
              "enroll_info AS t1",
              "LEFT JOIN order_apply AS t2 ON t1.order_id = t2.id",
              "LEFT JOIN order_detail AS t3 ON t2.id = t3.order_id ",
            "WHERE",
              "t1.deleted = 0 ",
              "AND t1.is_return = 0 ",
              "AND t2.`status` &lt;&gt; 2 ",
              "AND t1.add_date BETWEEN '${startDate}' and '${endDate}' ",
            "GROUP BY",
              "t1.student_id,",
              "t1.subjects_id ",
            ") AS tb01",
            "LEFT JOIN ( SELECT * FROM transcript WHERE year_part = ${scoreYearPart} AND quarter_num = ${scoreQuarterNum} AND exam_num = 3 GROUP BY student_id, subjects_id ) AS tb02 ON tb01.student_id = tb02.student_id ",
            "AND tb01.subjects_id = tb02.subjects_id ",
            "LEFT JOIN (select id,head_img,`name` from student) as tb03 on tb01.student_id = tb03.id",
            "WHERE 1=1",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "  and tb01.school_id_order in(${schoolId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "  and tb01.subjects_id = #{subjectsId}",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "  and tb01.year_class_id_order = #{yearClassId}",
            "</when>",
            "<when test='studentName!=null and studentName!=&apos;&apos; '>",
            "  and tb03.name like '%${studentName}%'",
            "</when>",
            "ORDER BY",
            "tb01.subjects_id,",
            "year_class_id_order ASC",
            "</script>"
    })
    public IPage<ResNewStuScore> newStudentScore(IPage<?> page, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId, @Param("yearClassId") String yearClassId, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("scoreYearPart") String scoreYearPart, @Param("scoreQuarterNum") String scoreQuarterNum,@Param("studentName") String studentName);
}
